{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw,订单完成_函数 as dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book(r'C:\\Users\\UUPT\\Desktop\\应付工作\\9月\\9月代理商提现\\9月代理商提现-合并.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=wb.sheets('9.24').range('G1:W100').options(pd.DataFrame).value.reset_index()\n",
    "df1=df1.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>确认金额</th>\n",
       "      <th>审核状态</th>\n",
       "      <th>打款状态</th>\n",
       "      <th>真实姓名</th>\n",
       "      <th>结算方式</th>\n",
       "      <th>结算账户</th>\n",
       "      <th>审核人</th>\n",
       "      <th>审核时间</th>\n",
       "      <th>审核备注</th>\n",
       "      <th>打款人</th>\n",
       "      <th>打款时间</th>\n",
       "      <th>打款备注</th>\n",
       "      <th>往来单位编码</th>\n",
       "      <th>往来单位</th>\n",
       "      <th>摘要</th>\n",
       "      <th>None</th>\n",
       "      <th>None</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3900.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>乐山华焱商务服务有限公司</td>\n",
       "      <td>农业银行</td>\n",
       "      <td>'22364901040030230</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010053</td>\n",
       "      <td>乐山代理商</td>\n",
       "      <td>支付乐山代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>泊头市锦航科技服务有限公司</td>\n",
       "      <td>工商银行</td>\n",
       "      <td>'0408020009300470696</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010329</td>\n",
       "      <td>泊头市代理商</td>\n",
       "      <td>支付泊头市代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29000.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>云澜科技（玉溪）有限公司</td>\n",
       "      <td>交通银行</td>\n",
       "      <td>'534899991013000003391</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010074</td>\n",
       "      <td>玉溪代理商</td>\n",
       "      <td>支付玉溪代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29000.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>山东盛年信息技术有限公司</td>\n",
       "      <td>兴业银行</td>\n",
       "      <td>'379010100100801122</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010148</td>\n",
       "      <td>淄博代理商</td>\n",
       "      <td>支付淄博代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1100.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>济宁茗智网络科技有限公司</td>\n",
       "      <td>农业银行</td>\n",
       "      <td>'15483101040046452</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010321</td>\n",
       "      <td>金乡县代理商</td>\n",
       "      <td>支付金乡县代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>5800.0</td>\n",
       "      <td>已审核</td>\n",
       "      <td>未打款</td>\n",
       "      <td>达州行将至科技有限公司</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>'51050175607000000228</td>\n",
       "      <td>刘杨</td>\n",
       "      <td>2024-09-24 10:14:00</td>\n",
       "      <td>1328520.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1899-12-31</td>\n",
       "      <td>None</td>\n",
       "      <td>J010066</td>\n",
       "      <td>达州代理商</td>\n",
       "      <td>支付达州代理商线上盈利提现</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       确认金额  审核状态  打款状态           真实姓名  结算方式                    结算账户   审核人  \\\n",
       "0    3900.0   已审核   未打款   乐山华焱商务服务有限公司  农业银行      '22364901040030230    刘杨   \n",
       "1    1000.0   已审核   未打款  泊头市锦航科技服务有限公司  工商银行    '0408020009300470696    刘杨   \n",
       "2   29000.0   已审核   未打款   云澜科技（玉溪）有限公司  交通银行  '534899991013000003391    刘杨   \n",
       "3   29000.0   已审核   未打款   山东盛年信息技术有限公司  兴业银行     '379010100100801122    刘杨   \n",
       "4    1100.0   已审核   未打款   济宁茗智网络科技有限公司  农业银行      '15483101040046452    刘杨   \n",
       "..      ...   ...   ...            ...   ...                     ...   ...   \n",
       "94   5800.0   已审核   未打款    达州行将至科技有限公司  建设银行   '51050175607000000228    刘杨   \n",
       "95      NaN  None  None           None  None                    None  None   \n",
       "96      NaN  None  None           None  None                    None  None   \n",
       "97      NaN  None  None           None  None                    None  None   \n",
       "98      NaN  None  None           None  None                    None  None   \n",
       "\n",
       "                  审核时间       审核备注   打款人       打款时间  打款备注   往来单位编码    往来单位  \\\n",
       "0  2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010053   乐山代理商   \n",
       "1  2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010329  泊头市代理商   \n",
       "2  2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010074   玉溪代理商   \n",
       "3  2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010148   淄博代理商   \n",
       "4  2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010321  金乡县代理商   \n",
       "..                 ...        ...   ...        ...   ...      ...     ...   \n",
       "94 2024-09-24 10:14:00  1328520.0  None 1899-12-31  None  J010066   达州代理商   \n",
       "95                 NaT        NaN  None        NaT  None     None    None   \n",
       "96                 NaT        NaN  None        NaT  None     None    None   \n",
       "97                 NaT        NaN  None        NaT  None     None    None   \n",
       "98                 NaT        NaN  None        NaT  None     None    None   \n",
       "\n",
       "                摘要  None  None  \n",
       "0    支付乐山代理商线上盈利提现  None  None  \n",
       "1   支付泊头市代理商线上盈利提现  None  None  \n",
       "2    支付玉溪代理商线上盈利提现  None  None  \n",
       "3    支付淄博代理商线上盈利提现  None  None  \n",
       "4   支付金乡县代理商线上盈利提现  None  None  \n",
       "..             ...   ...   ...  \n",
       "94   支付达州代理商线上盈利提现  None  None  \n",
       "95            None  None  None  \n",
       "96            None  None  None  \n",
       "97            None  None  None  \n",
       "98            None  None  None  \n",
       "\n",
       "[99 rows x 17 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "代理商结算支付=dd.通用凭证模版(dd.clm,'应付凭证',摘要=df1['摘要'],科目编码='22413003',借贷方向='借方',本币=df1['确认金额'],\n",
    "               往来单位编码=df1['往来单位编码'],往来单位=df1['往来单位']) \n",
    "\n",
    "招商支付=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商线上盈利提现',科目编码='100207',借贷方向='贷方',本币=[df1['确认金额'].sum()])\n",
    "    \n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证=pd.concat([代理商结算支付,招商支付]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证.to_clipboard()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
